﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Data;
using System.IO;
using System.Runtime.Serialization;
using System.Runtime.Serialization.Formatters.Binary;
using System.IO.Compression;

namespace MTS
{
    class SqlCache : ChunkCache
    {
        SQLiteConnection connection;
        RenderSettings settings;
        private IFormatter formatter;
        String cacheName;

        private int queueSize = 1024;
        private List<TileImage> queue;

        public int QueueSize
        {
            get
            {
                return queueSize;
            }

            set
            {
                queueSize = value;
            }
        }

        public SqlCache(RenderSettings settings)
        {
            this.settings = settings;
            formatter = new BinaryFormatter();
            queue = new List<TileImage>();

            FileInfo cacheDir = new FileInfo(settings.ToString());
            if (!cacheDir.Directory.Exists)
            {
                cacheDir.Directory.Create();
            }

            cacheName = settings.ToString() + ".sqlite3";
            connection = new SQLiteConnection("Data Source=" + cacheName);
            connection.Open();
            createTables();
        }

        private void createTables()
        {
            lock (connection)
            {
                String createTileTableSql = "CREATE TABLE IF NOT EXISTS tiles ( filename TEXT PRIMARY KEY, image BLOB )";
                SQLiteCommand createCommand = connection.CreateCommand();
                createCommand.CommandText = createTileTableSql;
                createCommand.ExecuteNonQuery();
            }
        }

        public void addImage(TileImage image)
        {
            List<TileImage> copyQueue = new List<TileImage>();
            int copyNum = 0;
            lock (queue)
            {
                queue.Add(image);
                if (queue.Count > queueSize)
                {
                    copyNum = queue.Count;
                    copyQueue = queue.GetRange(0, copyNum);
                    queue.RemoveRange(0, copyNum);
                }
            }
            if (copyQueue.Count > 0)
            {
                // flush it,
                flushQueue(copyQueue);
            }
        }

        private void flushQueue()
        {
            List<TileImage> copyQueue = new List<TileImage>();
            int copyNum = 0;
            lock (queue)
            {
                copyNum = queue.Count;
                copyQueue = queue.GetRange(0, copyNum);
                queue.RemoveRange(0, copyNum);
            }
            if (copyQueue.Count > 0)
            {
                // flush it,
                flushQueue(copyQueue);
            }
        }

        private void flushQueue(List<TileImage> queue)
        {
            lock (connection)
            {
                SQLiteTransaction insertTransaction = connection.BeginTransaction();
#if DEBUG
                Console.Out.WriteLine("Starting Transaction");
#endif
                using (SQLiteCommand saveCommand = connection.CreateCommand())
                {
                    saveCommand.CommandText = "INSERT INTO tiles (filename, image) VALUES (@name, @image)";;
                    foreach (TileImage image in queue)
                    {
                        if (imageExists(image.WorldX, image.WorldY))
                        {
                            deleteImage(image.WorldX, image.WorldY);
                        }
                        using (MemoryStream s = new MemoryStream())
                        {

                                formatter.Serialize(s, image);

                                SQLiteParameter filenameParameter = new SQLiteParameter("@name");
                                filenameParameter.Value = uniqueCode(image.WorldX, image.WorldY);

                                SQLiteParameter imageParameter = new SQLiteParameter("@image");
                                imageParameter.Value = s.GetBuffer();


                                saveCommand.Parameters.Add(filenameParameter);
                                saveCommand.Parameters.Add(imageParameter);
                                saveCommand.ExecuteNonQuery();
                            
                        }
                    }
                }
                
                insertTransaction.Commit();
#if DEBUG
                Console.Out.WriteLine("Transaction Completed");
#endif
            }
        }

        public TileImage getImage(long worldX, long worldY)
        {
            lock (connection)
            {
                String findImageSql = String.Format("SELECT image FROM tiles WHERE filename = @name LIMIT 1");

                SQLiteParameter filenameParameter = new SQLiteParameter("@name");
                filenameParameter.Value = uniqueCode(worldX, worldY);

                SQLiteCommand findCommand = connection.CreateCommand();
                findCommand.CommandText = findImageSql;
                findCommand.Parameters.Add(filenameParameter);
                SQLiteDataReader reader = findCommand.ExecuteReader();
                DataTable dt = new DataTable();
                dt.Load(reader);
                reader.Close();
                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow r in dt.Rows)
                    {
                        try
                        {
                            byte[] image = (byte[])r["image"];
                            using (MemoryStream s = new MemoryStream(image))
                            {
                                TileImage cacheImage = (TileImage)formatter.Deserialize(s);
                                return cacheImage;
                            }
                        }
                        catch (SQLiteException e)
                        {
                            Console.Out.WriteLine(e);
                        }
                    }
                    throw new Exception("No items in cache");
                }
                else
                {
                    throw new FileNotFoundException("File not present in cache", uniqueCode(worldX, worldY));
                }
            }
        }

        public bool imageExists(long worldX, long worldY)
        {
            lock (connection)
            {
                String findImageSql = String.Format("SELECT filename FROM tiles WHERE filename = '{0}' LIMIT 1", uniqueCode(worldX, worldY));
                SQLiteCommand findCommand = connection.CreateCommand();
                findCommand.CommandText = findImageSql;
                SQLiteDataReader reader = findCommand.ExecuteReader();
                DataTable dt = new DataTable();
                dt.Load(reader);
                reader.Close();
                if (dt.Rows.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }

        public void Dispose()
        {
            flushQueue();
            connection.Close();
            connection.Dispose();
        }

        private string uniqueCode(long worldX, long worldY)
        {
            return "C" + worldX + "x" + worldY;
        }

        private int deleteImage(long worldX, long worldY)
        {
            String deleteSql = String.Format("DELETE FROM tiles WHERE filename = '{0}'", uniqueCode(worldX, worldY));
            SQLiteCommand deleteCmd = connection.CreateCommand();
            deleteCmd.CommandText = deleteSql;
            return deleteCmd.ExecuteNonQuery();
        }

        public override String ToString()
        {
            return cacheName;
        }
    }
}
